Imports nsDataProvider = KDESolutions.Providers.DataProvider
Imports System.Configuration
Imports System.Data.Common
Public Class DBCodeDecodeProvider
    Inherits CodeDecodeProvider
    Private Const cTrueValue = "Y"
    Private Const cFalseValue = "N"
    Private _DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString As String


    Public Overrides Sub Initialize(ByVal name As String, ByVal config As System.Collections.Specialized.NameValueCollection)
        If (config Is Nothing) Then
            Throw New ArgumentNullException("config")
        End If
        If String.IsNullOrEmpty(name) Then
            name = "CodeDecodeProvider"
        End If
        If String.IsNullOrEmpty(config("description")) Then
            config.Remove("description")
            config.Add("description", "The default CodeDecode Provider.")
        End If

        'Start of config file settings for database connection ******
        If String.IsNullOrEmpty("DataProviderGroup") Then
            Throw New ArgumentNullException("You must provide a DataProviderGroup")
        Else
            Dim DataProviderGroup As KDESolutions.Providers.DataProvider.DataProviderConfiguration = _
            CType(ConfigurationManager.GetSection(config("DataProviderGroup")), KDESolutions.Providers.DataProvider.DataProviderConfiguration)
            If String.IsNullOrEmpty(DataProviderGroup.DBType) = False Then
                _DBType = DataProviderGroup.DBType
            End If
            If String.IsNullOrEmpty(DataProviderGroup.DBServer) = False Then
                _DBServer = DataProviderGroup.DBServer
            End If
            If String.IsNullOrEmpty(DataProviderGroup.DBUser) = False Then
                _DBUser = DataProviderGroup.DBUser
            End If
            If String.IsNullOrEmpty(DataProviderGroup.DBPassword) = False Then
                _DBPassword = DataProviderGroup.DBPassword
            End If
            If String.IsNullOrEmpty(DataProviderGroup.DBDatabaseName) = False Then
                _DBDatabaseName = DataProviderGroup.DBDatabaseName
            End If
            If String.IsNullOrEmpty(DataProviderGroup.ConnectString) = False Then
                _ConnectString = DataProviderGroup.ConnectString
            End If
        End If

        MyBase.Initialize(name, config)

        'If any configuration attributes are left, throw an exception
        'If ((config.Count > 0) _
        '            AndAlso Not String.IsNullOrEmpty(config.GetKey(0))) Then
        '    Throw New System.Configuration.Provider.ProviderException("There are too many configuration attributes specified")
        'End If
    End Sub

    ''' <summary>
    ''' Returns the string value representing the Long Description of the given code/code category combination.
    ''' </summary>
    ''' <param name="CodeCatId">Intenger value indicating the category you are retreiving data for.</param>
    ''' <param name="CodeValue">The code value you want to decode from the lookup tables.</param>
    ''' <returns>String value representing the long description of the given code/code category combination.</returns>
    ''' <remarks>You can maintain code categories, and codes within the database by levering these classes.</remarks>
    Public Overrides Function GetLongDesc(ByVal CodeCatId As Integer, ByVal CodeValue As String) As String
        Try
            Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
            Dim returnVal As String = Nothing
            Dim strSQL As String = "SELECT CodeLongDesc FROM Codes Where CodeCategoryId = ? AND CodeValue = ? AND LogicalDeleteInd = ?"
            Dim result As String
            DAP.AddParameter("@CodeCategoryId", CodeCatId, DbType.Int64)
            DAP.AddParameter("@CodeValue", CodeValue, DbType.String)
            DAP.AddParameter("@LogicalDeleteInd", cFalseValue, DbType.String)
            result = DAP.ExecuteScalar(strSQL)
            Return result
        Catch Ex As Exception
            Return String.Empty
        Finally
        End Try
    End Function

    ''' <summary>
    ''' Returns the string value representing the Long Description of the given code/code category combination.
    ''' </summary>
    ''' <param name="CodeCatId">Integer value indicating the category you are retreiving data for.</param>
    ''' <param name="CodeId">The CodeId unique identifier you want to decode from the lookup tables.</param>
    ''' <returns>String value representing the long description of the given code/code category combination.</returns>
    ''' <remarks>You can maintain code categories, and codes within the database by levering these classes.</remarks>
    Public Overrides Function GetLongDesc(ByVal CodeCatId As Integer, ByVal CodeId As Integer) As String
        Try
            Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
            Dim returnVal As String = Nothing
            Dim strSQL As String = "SELECT CodeLongDesc FROM Codes Where CodeCategoryId = ? AND CodeId = ? AND LogicalDeleteInd = ?"
            Dim result As String
            DAP.AddParameter("@CodeCategoryId", CodeCatId, DbType.Int64)
            DAP.AddParameter("@CodeId", CodeId, DbType.Int64)
            DAP.AddParameter("@LogicalDeleteInd", cFalseValue, DbType.String)
            result = DAP.ExecuteScalar(strSQL)
            Return result
        Catch Ex As Exception
            Return String.Empty
        End Try
    End Function

    ''' <summary>
    ''' Returns the string value representing the Short Description of the given code/code category combination.
    ''' </summary>
    ''' <param name="CodeCatId">Intenger value indicating the category you are retreiving data for.</param>
    ''' <param name="CodeValue">The code value you want to decode from the lookup tables.</param>
    ''' <returns>String value representing the long description of the given code/code category combination.</returns>
    ''' <remarks>You can maintain code categories, and codes within the database by levering these classes.</remarks>
    Public Overrides Function GetShortDesc(ByVal CodeCatId As Integer, ByVal CodeValue As String) As String
        Try
            Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
            Dim returnVal As String = Nothing
            Dim strSQL As String = "SELECT CodeShortDesc FROM Codes Where CodeCategoryId = ? AND CodeValue = ? AND LogicalDeleteInd = ?"
            Dim result As String
            DAP.AddParameter("@CodeCategoryId", CodeCatId, DbType.Int64)
            DAP.AddParameter("@CodeValue", CodeValue, DbType.String)
            DAP.AddParameter("@LogicalDeleteInd", cFalseValue, DbType.String)
            result = DAP.ExecuteScalar(strSQL)
            Return result
        Catch Ex As Exception
            Return String.Empty
        End Try
    End Function

    ''' <summary>
    ''' Returns the string value representing the Short Description of the given code/code category combination.
    ''' </summary>
    ''' <param name="CodeCatId">Integer value indicating the category you are retreiving data for.</param>
    ''' <param name="CodeId">The CodeId unique identifier you want to decode from the lookup tables.</param>
    ''' <returns>String value representing the long description of the given code/code category combination.</returns>
    ''' <remarks>You can maintain code categories, and codes within the database by levering these classes.</remarks>
    Public Overrides Function GetShortDesc(ByVal CodeCatId As Integer, ByVal CodeId As Integer) As String
        Try
            Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
            Dim returnVal As String = Nothing
            Dim strSQL As String = "SELECT CodeLongDesc FROM Codes Where CodeCategoryId = ? AND CodeId = ? AND LogicalDeleteInd = ?"
            Dim result As String
            DAP.AddParameter("@CodeCategoryId", CodeCatId, DbType.Int64)
            DAP.AddParameter("@CodeId", CodeId, DbType.Int64)
            DAP.AddParameter("@LogicalDeleteInd", cFalseValue, DbType.String)
            result = DAP.ExecuteScalar(strSQL)
            Return result
        Catch Ex As Exception
            Return String.Empty
        End Try
    End Function

    ''' <summary>
    ''' The GetAllCodeCategories method will return a DataTable containing all values from the database.
    ''' </summary>
    ''' <returns>Data.DataTable</returns>
    ''' <remarks>There are multiple ways to get the CodeList you can pass it a Enumerator of CodeDecode.CodeCategory, or a CategoryId, or with no 
    ''' parameters. If you call it with no parameters you will get all rows back from the database.</remarks>
    Public Overrides Function GetAllCodeCategories() As Data.DataTable
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim dtReturnTable As New Data.DataTable("CodeCategories")
        Dim sql As String = "SELECT * FROM CodeCategories WHERE LogicalDeleteInd = ? ORDER BY CodeCategoryDesc"
        DAP.AddParameter("@LogicalDeleteInd", cFalseValue, DbType.String)
        dtReturnTable = DAP.ExecuteDataSet(sql).Tables(0)
        Return dtReturnTable
    End Function

    Public Overrides Function GetCodeList(ByVal CategoryId As Integer, ByVal RetrieveForDropdown As Boolean) As Data.DataTable
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim dtReturnTable As New Data.DataTable("Codes")
        Dim sql As String
        sql = "SELECT * FROM Codes WHERE LogicalDeleteInd = ? AND CodeCategoryId = ? "
        If RetrieveForDropdown Then
            sql = sql & "AND IncludeInDropDown = ? ORDER BY SortOrder, CodeShortDesc"
        Else
            sql = sql & "ORDER BY SortOrder, CodeShortDesc"
        End If
        DAP.AddParameter("@LogicalDeleteInd", cFalseValue, DbType.String)
        DAP.AddParameter("@CodeCategoryId", CategoryId, DbType.Int64)
        If RetrieveForDropdown Then
            DAP.AddParameter("@IncludeInDropDown", cTrueValue, DbType.String)
        End If
        dtReturnTable = DAP.ExecuteDataSet(sql).Tables(0)
        Return dtReturnTable
    End Function

    ''' <summary>
    ''' The GetCodeList method returns a DataTabel with all the codes from the database
    ''' </summary>
    ''' <param name="CategoryId">Integer value of the category for which you want to retrieve codes for.</param>
    ''' <returns>DataTable containing all of the code values for the given CategoryId</returns>
    ''' <remarks>You can bind DataTable objects to controls</remarks>
    Public Overrides Function GetCodeList(ByVal CategoryId As Integer) As Data.DataTable
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim dtReturnTable As New Data.DataTable("Codes")
        Dim sql As String = "SELECT * FROM Codes WHERE CodeCategoryId = ? AND LogicalDeleteInd = ? ORDER BY SortOrder, CodeShortDesc"
        DAP.AddParameter("@CodeCateogryId", CategoryId, DbType.Int64)
        DAP.AddParameter("@LogicalDeleteInd", cFalseValue, DbType.String)
        dtReturnTable = DAP.ExecuteDataSet(sql).Tables(0)
        Return dtReturnTable
    End Function

    ''' <summary>
    ''' The GetCodeList method returns a DataTabel with all the codes from the database
    ''' </summary>
    ''' <returns>DataTable containing all of the code values</returns>
    ''' <remarks>There are multiple ways to get the CodeList you can pass it a Enumerator of CodeDecode.CodeCategory, or a CategoryId, or with no 
    ''' parameters. If you call it with no parameters you will get all rows back from the database.</remarks>
    Public Overrides Function GetCodeList() As Data.DataTable
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim dtReturnTable As New Data.DataTable("Codes")
        Dim sql As String = "SELECT * FROM Codes WHERE LogicalDeleteInd = ?"
        DAP.AddParameter("@LogicalDeleteInd", cFalseValue, DbType.String)
        dtReturnTable = DAP.ExecuteDataSet(sql).Tables(0)
        Return dtReturnTable
    End Function

    Public Overrides Function GetUserSettings(ByVal UserId As String) As Hashtable
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim ReturnHashTable As New Hashtable
        Try
            Dim sqlGetUserDefaults As String = "SELECT SettingKey, SettingValue FROM MembershipUserSettings " & _
            "WHERE UserId = ? AND LogicalDeleteInd = ?"
            DAP.AddParameter("@UserId", UserId, DbType.String)
            DAP.AddParameter("@LogicalDeleteInd", cFalseValue, DbType.String)
            Dim drReader As DbDataReader
            drReader = DAP.ExecuteReader(sqlGetUserDefaults)
            If drReader.HasRows Then
                Do While drReader.Read
                    ReturnHashTable.Add(drReader.GetValue(0), drReader.GetValue(1))
                Loop
            End If
            Return ReturnHashTable
        Catch ex As Exception
            Return Nothing
        End Try
    End Function
End Class
